前幾篇的實作都是從HR或OE帳戶中查詢,這篇的實作帳戶使用自己新建的Hotel帳戶命名、新增並查詢。
create table housekeepers
(
housekeeper_id NUMBER(5)primary key,
housekeeper_name NCHAR(20) not null,
housekeeper_phonenumber NUMBER(12) not null,
housekeeper_roomsituation varchar(8) not null,
housekeeper_boss NCHAR(5) not null
);
接下來要自己新增資料至資料表中:
insert into housekeepers values(456,'Ivy','0933123456','Done','Basia');
insert into housekeepers values(457,'Adele','0933168756','Done','Basia');
insert into housekeepers values(458,'Cara','0933123772','Done','Basia');
insert into housekeepers values(459,'Eden','0938123909','Cleaning','Basia');
insert into housekeepers values(460,'Rose','0933123342','Done','Basia');
insert into housekeepers values(461,'Allen','0938120019','Done','Basia');
insert into housekeepers values(462,'Celine','0933180624','Cleaning','Basia');
insert into housekeepers values(463,'Eve','0938123906','Cleaning','Basia');
insert into housekeepers values(464,'Gail','0933703342','Done','Basia');
insert into housekeepers values(465,'Hedy','0938127249','Undone','Basia');
insert into housekeepers values(466,'Kate','0943180624','Cleaning','Jolie');
insert into housekeepers values(467,'Kally','0943180545','Cleaning','Jolie');
insert into housekeepers values(468,'Grace','0943183124','Cleaning','Jolie');
insert into housekeepers values(469,'May','0943167925','Done','Jolie');
insert into housekeepers values(570,'Gray','0943141314','Cleaning','Jolie');
insert into housekeepers values(571,'Apple','0943188795','Done','Jolie');
insert into housekeepers values(572,'Kay','0978983124','Cleaning','Jolie');
insert into housekeepers values(573,'Novia','0943167675','Done','Jolie');
insert into housekeepers values(574,'Sharon','0943177645','Cleaning','Jolie');
insert into housekeepers values(575,'Pearl','0943189324','Undone','Jolie');
insert into housekeepers values(576,'Una','0943180321','Cleaning','Tammy');
insert into housekeepers values(577,'Winnie','0943180098','Cleaning','Tammy');
insert into housekeepers values(578,'Orange','0943183112','Cleaning','Tammy');
insert into housekeepers values(579,'Stella','0948357925','Done','Tammy');
insert into housekeepers values(580,'Ruby','0945381314','Cleaning','Tammy');
insert into housekeepers values(581,'Jennie','0967588795','Done','Tammy');
insert into housekeepers values(582,'Polly','0978914869','Cleaning','Tammy');
insert into housekeepers values(583,'Pag','0943190525','Done','Tammy');
insert into housekeepers values(584,'Myra','0943177365','Cleaning','Tammy');
insert into housekeepers values(585,'Lisa','0943189878','Undone','Tammy');
而如果TABLE是新增在預設的帳戶中,隨後才新增自己的帳戶,要將表格轉移至HOTEL帳戶輸入以下程式碼即可:
GRANT CREATE ANY TABLE TO Hotel;
輸入一SQL語句檢查是否新增成功,結果如下:
SELECT room_id,housekeeper_id,housekeeper_phonenumber,housekeeper_boss
from housekeepers join rooms
USING(housekeeper_id);